/*
 * Copyright (c) 2010, James Daniello
 * This project is licensed under the 'New BSD' license.
 * The full text of the license can be found in the root
 * directory under the filename "LICENSE"
 */
package src.network;

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import src.engine.PICAIUDice;

/**
 * This class represents an active connection to the PICAIU MYSql database. From here the server
 * can pull one of these from it's connection pool and connect to the database.
 */
public class PICAIUDBConnection {

  //mySQL Database connection
  private Connection dbConn;
  private boolean connected = false;

  public PICAIUDBConnection() {
    connected = openDBConnection();
  }

  /**
   * Returns if a connection was established and is currently maintained.
   */
  public boolean isConnected() {
    return connected;
  }

  /**
   * Returns a boolean if a connection to the MySQL database was successful. It gets the database, username, and password
   * from the server.
   */
  protected boolean openDBConnection() {
    try {
      String db = "jdbc:mysql://localhost/" + PICAIUServer.database + "?user=" + PICAIUServer.dbUser + "&password=" + PICAIUServer.dbPass;
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      dbConn = DriverManager.getConnection(db);
      return true;
    } catch (Exception e) {
      System.out.println(e);
    }
    return false;
  }

  /**
   * After the request for the username has been returned from the client, the username is verified in the
   * database and if the user/pass combo is not acceptable removes the persona from the server.
   */
  public boolean verifyUserInDB(String user, String password, PICAIUServerPersona persona) {
    if (dbConn != null) {
      try {
        //returns the user data from db
        Statement sqlstmt = dbConn.createStatement();
        sqlstmt.executeQuery("SELECT * from users where LOWER(username) = LOWER('" + user + "')");
        ResultSet resultSet = sqlstmt.getResultSet();
        if (!resultSet.next()) {
          persona.sendDataToClient("ERR#DB");
          return false;
        }

        String userNext = resultSet.getString("username");
        String passwordNext = resultSet.getString("password");
        String idNext = resultSet.getString("id");
        if (password.equals(passwordNext)) {
          persona.setUserName(userNext);
          persona.setId(Integer.parseInt(idNext));

          Statement sqlstmt2 = dbConn.createStatement();
          sqlstmt2.executeQuery("SELECT * from stats where id = " + idNext);
          resultSet = sqlstmt2.getResultSet();
          resultSet.next();
          persona.setMoney(resultSet.getInt("money"));
          return true;
        }

        //if 'true' has not been returned then user is not in database so remove connection
        persona.sendDataToClient("ERR#DB");
        //sendDataToClient("DSCNT");
        System.out.println(persona.getUserName() + " could not be verified in database");
      } catch (Exception e) {
        //if error occurs then remove connection
        persona.sendDataToClient("ERR#DB");
        //sendDataToClient("DSCNT");
        System.out.println(persona.getUserName() + " could not be verified in database");
        System.out.println(e);
      }
    }
    return false;
  }

  /**
   * Attempts to create a new user in the database and returns ##OK## if successful, or else
   * returns an error message
   * @param userIn the desired username
   * @param passwordIn the desired password
   * @param emailIn the desired email address
   */
  protected String createNewUser(String userIn, String passwordIn, String emailIn) {

    if (dbConn != null) {
      try {
        //returns the set of all users in the database
        Statement sqlstmt = dbConn.createStatement();
        sqlstmt.executeQuery("SELECT * from users where LOWER( username ) = LOWER( '" + userIn + "')");
        ResultSet resultSet = sqlstmt.getResultSet();
        if (resultSet.next()) {
          return "user name already exists";
        }


        sqlstmt = dbConn.createStatement();
        sqlstmt.executeQuery("SELECT * from users where LOWER( email ) = LOWER( '" + emailIn + "')");
        resultSet = sqlstmt.getResultSet();

        if (resultSet.next()) {
          return "email is already in use";
        }


        //if got this far than it is alright to create the user
        Statement sqlstmt2 = dbConn.createStatement();
        sqlstmt2.execute("insert into users (username, password, email) values (\"" + userIn + "\",\"" + passwordIn + "\",\"" + emailIn + "\");");



        Statement sqlstmt3 = dbConn.createStatement();
        sqlstmt3.execute("select * from users where lower(username) = lower('" + userIn + "')");
        sqlstmt3.getResultSet().next();
        int id = sqlstmt3.getResultSet().getInt("id");

        Statement sqlstmt4 = dbConn.createStatement();
        sqlstmt4.execute("insert into stats (money, wins, losses, id) values (\"200\",\"0\",\"0\",\"" + id + "\");");

        createNewTroopSet(dbConn);


        return "##OK##";

      } catch (Exception e) {
        //if error occurs then remove connection
        System.out.println(e);
      }
    }


    return "db error. Please try again.";


  }

  /**
   * Attempts to change a password for a user in the database and returns ##OK## if successful, or else
   * returns an error message
   * @param passwordIn the desired password
   * @param emailIn the desired email address
   */
  protected String changePassword(String passwordIn, String emailIn) {

    if (dbConn != null) {
      try {
        Statement sqlstmt2 = dbConn.createStatement();


        boolean perform = sqlstmt2.execute(" UPDATE users SET password='" + passwordIn + "' WHERE email='" + emailIn + "';");


        return "##OK##";


      } catch (Exception e) {
        //if error occurs then remove connection
        System.out.println(e);


      }
    }
    return "db error. Please try again.";


  }

  /**
   * This method begins the process of recovering user info by emailing the username to the
   * email address in the database as well as a special verification code that must match
   * the one created from this method then resets the password.
   * @param emailIn the email address to check for then email
   */
  protected String recover(String emailIn, PICAIUServerPersona persona) {

    if (dbConn != null) {
      try {
        //returns the set of all users in the database
        Statement sqlstmt = dbConn.createStatement();
        System.out.println(emailIn);
        sqlstmt.executeQuery("SELECT * from users where LOWER(email) = LOWER('" + emailIn + "');");
        ResultSet resultSet = sqlstmt.getResultSet();
        if (!resultSet.next()) {
          return "email not in database.";
        }
        //iterate through all the usernames and look for a match

        String userNext = resultSet.getString("username");
        String passwordNext = resultSet.getString("password");
        String emailNext = resultSet.getString("email");
        persona.generateVerificationCode();
        if (persona.emailRecover(userNext, emailNext)) {
          return "##OK##";
        } else {
          return "server error. Please try again.";
        }

      } catch (Exception e) {
        //if error occurs then remove connection
        System.out.println(e);


      }
    }
    return "db error. Please try again.";

  }

  /*
   * This method populates the database with new soldiers when a new user is created
   */
  private boolean createNewTroopSet(Connection dbConn) throws SQLException {
    Statement s = dbConn.createStatement();
    s.execute("select count(*) from users");
    s.getResultSet().next();
    int num = s.getResultSet().getInt("count(*)");

    int total = (int) Math.ceil(30 - (Math.log(num) * 2));

    int numGunners = (int) Math.ceil(total * .4);
    int numTanks = (int) Math.ceil(total * .3);
    int numRocketeers = (int) Math.ceil(total * .3);

    //default values for units
    int health, energy, accuracy, vision, curiousity, bravery, worth;

    //default values for gunnners
    health = 100;
    energy = 50;
    accuracy = 60;
    vision = 240;
    curiousity = 30;
    bravery = 30;
    worth = 10;

    //create gunners
    Statement[] sts = new Statement[numGunners];
    for (int i = 0; i < numGunners; i++) {
      sts[i] = dbConn.createStatement();
      sts[i].execute("insert into soldiers (typeofunit, money, health, energy, accuracy, vision, curiousity, bravery, worth) values (\"0\",\"0\",\""
              + health + "\",\"" + energy + "\",\"" + accuracy + "\",\"" + vision + "\",\"" + curiousity + "\",\"" + bravery + "\",\"" + worth + "\");");

    }


    //default values for tanks
    health = 300;
    energy = 40;
    accuracy = 70;
    vision = 290;
    curiousity = 50;
    bravery = 50;
    worth = 20;


    //create tanks
    Statement[] sts2 = new Statement[numTanks];
    for (int i = 0; i < numTanks; i++) {
      sts2[i] = dbConn.createStatement();
      sts2[i].execute("insert into soldiers (typeofunit, money, health, energy, accuracy, vision, curiousity, bravery, worth) values (\"1\",\"0\",\""
              + health + "\",\"" + energy + "\",\"" + accuracy + "\",\"" + vision + "\",\"" + curiousity + "\",\"" + bravery + "\",\"" + worth + "\");");

    }


    //default values for rocketeers
    health = 100;
    energy = 50;
    accuracy = 40;
    vision = 240;
    curiousity = 40;
    bravery = 40;
    worth = 15;

    //create rocketeers
    Statement[] sts3 = new Statement[numRocketeers];
    for (int i = 0; i < numRocketeers; i++) {
      sts3[i] = dbConn.createStatement();
      sts3[i].execute("insert into soldiers (typeofunit, money, health, energy, accuracy, vision, curiousity, bravery, worth) values (\"2\",\"0\",\""
              + health + "\",\"" + energy + "\",\"" + accuracy + "\",\"" + vision + "\",\"" + curiousity + "\",\"" + bravery + "\",\"" + worth + "\");");
    }

    return true;
  }

  PICAIURecruittedUnit recruitUnit(int type, int amount, PICAIUServerPersona owner) {
    try {
      Statement sqlstmt = dbConn.createStatement();
      sqlstmt.executeQuery("SELECT * from soldiers where typeofunit = " + type + " and worth <= " + amount + " and ingame = 0 order by worth desc limit 30;");
      ResultSet results = sqlstmt.getResultSet();
      if (!results.next()) {
        return null;
      }
      int i = PICAIUDice.roll(29);
      while (i > 0) {
        i--;
        if (!results.next()) {
          results.last();
          break;
        }
      }
      long id = results.getLong("id");
      int health = results.getInt("health");
      int energy = results.getInt("energy");
      int vision = results.getInt("vision");
      int accuracy = results.getInt("accuracy");
      int curiousity = results.getInt("curiousity");
      int bravery = results.getInt("bravery");
      int worth = results.getInt("worth");

      PICAIURecruittedUnit unit = new PICAIURecruittedUnit(id, type, worth, health, energy, vision, accuracy, curiousity, bravery, owner);
      sqlstmt = dbConn.createStatement();
      sqlstmt.executeUpdate("UPDATE soldiers SET ingame = 1 where id = " + id + ";");


      return unit;


    } catch (SQLException ex) {
      Logger.getLogger(PICAIUDBConnection.class.getName()).log(Level.SEVERE, null, ex);
      return null;
    }
  }

  /**
   * Cleans up the database (e.g. puts all soldiers back in the pool)
   */
  void cleanUp() {
    Statement sqlstmt;
    try {
      sqlstmt = dbConn.createStatement();
      sqlstmt.executeUpdate("update soldiers set ingame = 0;");
    } catch (SQLException ex) {
      Logger.getLogger(PICAIUDBConnection.class.getName()).log(Level.SEVERE, null, ex);
    }

  }

  void putSoldierBack(long id) {
    Statement sqlstmt;
    try {
      sqlstmt = dbConn.createStatement();
      sqlstmt.executeUpdate("update soldiers set ingame = 0 where id = " + id + ";");
    } catch (SQLException ex) {
      Logger.getLogger(PICAIUDBConnection.class.getName()).log(Level.SEVERE, null, ex);
    }

  }
}
